﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.IO;
using NPOI;
using NPOI.HSSF;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Data.Common;
using System.Data.Entity;
namespace HRWEBExport
{
    
    public partial class Form1 : Form
    {
        EntityConnection econ = null;
        public Form1()
        {
            InitializeComponent();
            econ = new EntityConnection(GetConnectionString());
        }
        public static string GetConnectionString()
        {
            string connectionString = new EntityConnectionStringBuilder
            {
                Metadata = "res://*/HRDB.csdl|res://*/HRDB.ssdl|res://*/HRDB.msl",
                Provider = "System.Data.SqlClient",
                ProviderConnectionString = new SqlConnectionStringBuilder
                {
                    InitialCatalog = "hrdb",
                    //DataSource = "192.168.246.7",
                    DataSource="woutsrv",
                    IntegratedSecurity = false,
                    UserID = "ybj",
                    Password = "0813rs#",
                    MultipleActiveResultSets = true,
                }.ConnectionString
            }.ConnectionString;

            return connectionString;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel2003文件|*.xls";
            DialogResult dialogResult = sfd.ShowDialog();
            if (dialogResult == DialogResult.OK)
            {
            string template = Path.Combine(Application.StartupPath,"admin核对打印表.xls");
            HSSFWorkbook wb = null;
            ISheet sheet1 = null;
            try
            {
                FileStream fs = new FileStream(template, FileMode.Open, FileAccess.Read);
                wb = new HSSFWorkbook(fs);
            }
            catch (IOException)
            {
                MessageBox.Show("服务器正忙，请稍后再试");
            }
            sheet1 = wb.GetSheet("sheet1");
            int i = 2;
            //var s_emp = ent.TEmployees.Where(p => p.TInfoSummaries != null);
            //s_emp = s_emp.Where(p => p.TEmployeeStatus.OrderByDescending(q => q.FOperateDate).FirstOrDefault().FStatusCode == "01").OrderBy(p => p.FEmployeeID);
            //s_emp = s_emp.Take(100);
            int pageSize = 200;
            int currentPage = 0;
            int totalSize = 0;
            using (hrdbEntities ent = new hrdbEntities(econ,false))
            {
                
                totalSize = ent.TEmployees.Where(p => p.FPublicID != null && p.FPublicID != ""&&p.TEmployeeStatus.OrderByDescending(q=>q.FOperateDate).FirstOrDefault().FStatusCode=="01").Count();
                
            }
                
            while (pageSize * currentPage < totalSize)
            {
                using (hrdbEntities ent1 = new hrdbEntities(econ,false))
                {
                    var s_emp = ent1.TEmployees.AsNoTracking().Where(p => p.FPublicID != null && p.FPublicID != ""&&p.TEmployeeStatus.OrderByDescending(q=>q.FOperateDate).FirstOrDefault().FStatusCode=="01").OrderBy(p => p.FEmployeeID).Skip(pageSize * currentPage).Take(pageSize);
                    foreach (var emp in s_emp)
                    {
                        IRow row = sheet1.CreateRow(i);
                        row.CreateCell(0).SetCellValue(i - 1);
                        row.CreateCell(1).SetCellValue(emp.GetKQDeptName());
                        row.CreateCell(2).SetCellValue(emp.FName);
                        row.CreateCell(3).SetCellValue(emp.FEmployeeID);
                        row.CreateCell(4).SetCellValue(emp.FPublicID);
                        row.CreateCell(5).SetCellValue(emp.FIntoHisDate.HasValue ? emp.FIntoHisDate.Value.ToString("yyyy-MM-dd") : "");
                        row.CreateCell(6).SetCellValue(emp.GetBelongOrgan());
                        row.CreateCell(7).SetCellValue(emp.Feedback());
                        row.CreateCell(8).SetCellValue(emp.IsSubmitted());
                        row.SetCellBorder();
                        row.Cells[7].CellStyle.WrapText = true;
                        //int cRow = emp.Feedback().Length / 35;
                        //row.HeightInPoints = 20;
                        i++;
                    }
                    currentPage++;
                }
            }
            
                FileStream ofs = File.Create(sfd.FileName);
                wb.Write(ofs);
                ofs.Close();
                ofs.Dispose();
            }
            MessageBox.Show("导出完成");
        }

        private void Form1_FormClosed(object sender, FormClosedEventArgs e)
        {
            econ.Close();
            econ.Dispose();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()),true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.TEduStories.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.TEduStories.AsNoTracking().OrderBy(p => p.FSerialNo).Skip(page * current).Take(page);
                foreach (HRJP.TEduStory t in ts)
                {
                    if (t.xlzs != "" && t.xlzs != null)
                    {
                        TEduStory tt = hrdb.TEduStories.Find(t.FSerialNo);
                        if (tt != null)
                        {
                            if (t.FOperateDate == tt.FOperateDate)
                            {
                                tt.Filename = t.xlzs;
                                hrdb.SaveChanges();
                            }
                        }
                    }
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin!");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.TDegrees.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.TDegrees.AsNoTracking().OrderBy(p => p.FSerialNo).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    if (t.xwzs != "" && t.xwzs != null)
                    {
                        var tt = hrdb.TDegrees.Find(t.FSerialNo);
                        if (tt != null)
                        {
                            if (t.FOperateDate == tt.FOperateDate)
                            {
                                tt.Filename = t.xwzs;
                                hrdb.SaveChanges();
                            }
                        }
                    }
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin!");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.TTitles.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.TTitles.AsNoTracking().OrderBy(p => p.FSerialNo).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    if (t.zgzs != "" && t.zgzs != null)
                    {
                        var tt = hrdb.TTitles.Find(t.FSerialNo);
                        if (tt != null)
                        {
                            if (t.FOperateDate == tt.FOperateDate)
                            {
                                tt.Filename = t.zgzs;
                                hrdb.SaveChanges();
                            }
                        }
                    }
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin!");
        }

        private void button5_Click(object sender, EventArgs e)
        {

        }

        private void button6_Click(object sender, EventArgs e)
        {
            
        }

        private void button6_Click_1(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPThesis.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPThesis.AsNoTracking().OrderBy(p => p.gzbm).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    string id = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID).Single();
                    Thesis thesis = hrdb.Theses.Create();
                    thesis.EmployeeID = id;
                    DateTime pub;
                    if (DateTime.TryParse(t.fbsj, out pub))
                        thesis.Date = pub;
                    else
                        continue;
                    if (pub < DateTime.Parse("1900-01-01"))
                        continue;
                    thesis.Filename = t.lwsmj;
                    thesis.MagazineIndexCode = hrdb.DictThesisMagazineIndexes.Where(p => p.FName == t.sl).Select(p => p.MagazineIndexCode).SingleOrDefault();
                    var kk = hrdb.Magazines.Where(p => p.Name == t.qkmc).Select(p => p.SerialNO);
                    if (!kk.Any()) continue;
                    thesis.MSerialNO = kk.Single();
                    thesis.AuthorOrderCode = hrdb.DictThesisAuthorOrders.Where(p => p.FName == t.wc).Select(p => p.OrderCode).SingleOrDefault();
                    thesis.Title = t.tm;
                    thesis.ThesisTypeCode = hrdb.DictThesisTypes.Where(p => p.FName == t.lb).Select(p => p.TypeCode).SingleOrDefault();
                    hrdb.Theses.Add(thesis);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button7_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPHonoraryTitles.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPHonoraryTitles.AsNoTracking().OrderBy(p => p.gzbm).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    string id = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID).Single();
                    HonoraryTitle ht = hrdb.HonoraryTitles.Create();
                    ht.EmployeeID = id;
                    DateTime pub;
                    if (DateTime.TryParse(t.sj, out pub))
                        ht.Date = pub;
                    else
                        continue;
                    if(pub<DateTime.Parse("1900-01-01"))
                        continue;
                    ht.Filename = t.ryzs;
                    ht.GradeCode = hrdb.DictHonoraryTitleGrades.Where(p => p.FName == t.lb).Select(p => p.FGradeCode).SingleOrDefault();
                    ht.Name = t.mc;
                    ht.IssuedDepartment = t.bfbm;
                    hrdb.HonoraryTitles.Add(ht);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button8_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPInflunceFactors.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPInflunceFactors.AsNoTracking().OrderBy(p => p.qkmc).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    if (!hrdb.Magazines.Where(p => p.Name == t.qkmc).Any())
                    {
                        Magazine m = hrdb.Magazines.Create();
                        m.Name = t.qkmc;
                        if (t.qkbs == "SCI") t.qkbs = "国际期刊";
                        else t.qkbs = "国内期刊";
                        m.TypeCode = hrdb.DictMagazineTypes.Where(p => p.FName == t.qkbs).Select(p => p.FTypeCode).SingleOrDefault();
                        hrdb.Magazines.Add(m);
                        hrdb.SaveChanges();
                    }
                    Magazine mm = hrdb.Magazines.Where(p => p.Name == t.qkmc).Single();
                    var kk = mm.MagazineInflunceFactors.Where(p => p.Year == t.nf);
                    if (kk.Any()) continue;
                    MagazineInflunceFactor mf = hrdb.MagazineInflunceFactors.Create();
                    mf.MSerialNO = mm.SerialNO;
                    mf.Year = (int)t.nf;
                    decimal yyz;
                    if (decimal.TryParse(t.yz, out yyz))
                        mf.Factor = yyz;
                    else
                        continue;
                    mf.GradeCode = hrdb.DictMagazineGrades.Where(p => p.FName == t.qklb).Select(p => p.FGradeCode).SingleOrDefault();
                    hrdb.MagazineInflunceFactors.Add(mf);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button9_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPThesis.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPThesis.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    string id = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID).Single();
                    Thesis thesis = hrdb.Theses.Create();
                    thesis.EmployeeID = id;
                    thesis.AuthorOrderCode = hrdb.DictThesisAuthorOrders.Where(p => p.FName == t.wc).Select(p => p.OrderCode).SingleOrDefault();
                    DateTime da;
                    if (!DateTime.TryParse(t.fbsj, out da)||da<DateTime.Parse("1900-01-01")) continue;
                    thesis.Date = da;
                    thesis.Filename = t.lwsmj;
                    thesis.MagazineIndexCode = hrdb.DictThesisMagazineIndexes.Where(p => p.FName == t.sl).Select(p => p.MagazineIndexCode).SingleOrDefault();
                    var maga = hrdb.Magazines.Where(p => p.Name == t.qkmc);
                    if (!maga.Any()) continue;
                    thesis.MSerialNO = maga.Single().SerialNO;
                    thesis.ThesisTypeCode = hrdb.DictThesisTypes.Where(p => p.FName == t.lb).Select(p => p.TypeCode).SingleOrDefault();
                    thesis.Title = t.tm;
                    hrdb.Theses.Add(thesis);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button10_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPPublishedBooks.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPPublishedBooks.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    string id = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID).Single();
                    PublishedBook book = hrdb.PublishedBooks.Create();
                    book.AuthorOrderCode = hrdb.DictBookAuthorOrders.Where(p => p.FName == t.wc).Select(p => p.OrderCode).SingleOrDefault();
                    book.AuthorTypeCode = hrdb.DictBookAuthorTypes.Where(p => p.FName == t.zzlb).Select(p => p.AuthorTypeCode).SingleOrDefault();
                    book.BookWordsCode = hrdb.DictBookWords.Where(p => p.FName == t.zs).Select(p => p.WordsCode).SingleOrDefault();
                    if (book.BookWordsCode == null) continue;
                    DateTime da;
                    if (!DateTime.TryParse(t.cbsj, out da)||da<DateTime.Parse("1900-01")) continue;
                    book.Date = da;
                    book.EmployeeID = id;
                    book.Filename = t.zzsmj;
                    book.Name = t.sm;
                    book.PressGradeCode = hrdb.DictPressGrades.Where(p => p.FName == t.cbsjb).Select(p => p.FGradeCode).SingleOrDefault();
                    if (book.PressGradeCode == null) continue;
                    book.PressName = t.cbsmc;
                    hrdb.PublishedBooks.Add(book);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button11_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPResearchProjects.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPResearchProjects.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    ResearchProject rp = hrdb.ResearchProjects.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj+"-01", out da)||da<DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    rp.Department = t.lxbm;
                    rp.EmployeeID = id;
                    rp.Filename = t.lxzs;
                    if (t.sjbk == null) t.sjbk = 0;
                    rp.Funds = (decimal)t.sjbk;
                    rp.GradeCode = hrdb.DictResearchGrades.Where(p => p.FName == t.jb).Select(p => p.FGradeCode).SingleOrDefault();
                    rp.Name = t.tm;
                    rp.Ordinal = int.Parse(t.wc);
                    rp.TypeCode = hrdb.DictResearchProjectTypes.Where(p => p.FName == t.ktlx).Select(p => p.FTypeCode).SingleOrDefault();
                    hrdb.ResearchProjects.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button12_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPResearchIdentifications.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPResearchIdentifications.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    ResearchIdentification rp = hrdb.ResearchIdentifications.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj + "-01", out da) || da < DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    if (string.IsNullOrEmpty(t.jddw)) continue;
                    rp.Department = t.jddw;
                    rp.EmployeeID = id;
                    rp.Filename = t.jdzs;
                    rp.GradeCode = hrdb.DictResearchIdentificationGrades.Where(p => p.FName == t.jdsp).Select(p => p.FGradeCode).SingleOrDefault();
                    rp.Name = t.tm;
                    rp.Ordinal = int.Parse(t.wc);
                    hrdb.ResearchIdentifications.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button13_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            DialogResult diares = sfd.ShowDialog();
            if (diares == DialogResult.OK)
            {
                HSSFWorkbook wb = new HSSFWorkbook();
                ISheet sheet = wb.CreateSheet("sheet1");
                hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
                int total = hrdb.TEmployees.Where(p => p.TEmployeeStatus.OrderByDescending(q => q.FOperateDate).FirstOrDefault().FStatusCode == "01").Count();
                int page = 300;
                int current = 0;
                int rownum = 0;
                while (page * current < total)
                {
                    var ts = hrdb.TEmployees.AsNoTracking().OrderBy(p => p.FEmployeeID).Where(p => p.TEmployeeStatus.OrderByDescending(q => q.FOperateDate).FirstOrDefault().FStatusCode == "01").Skip(page * current).Take(page).Select(p=>p.FEmployeeID);
                    foreach (var t in ts)
                    {
                        IRow row = sheet.CreateRow(rownum);
                        row.CreateCell(0).SetCellValue(t);
                        row.CreateCell(1).SetCellValue(hrdb.TEduStories.Where(p => p.FEmployeeID == t).OrderBy(p => p.FValidDate).Select(p => p.TDictEduLevel.FName).FirstOrDefault());
                        rownum++;
                    }
                    current++;
                }
                FileStream fs = new FileStream(sfd.FileName, FileMode.OpenOrCreate);
                wb.Write(fs);
                fs.Close();
                fs.Dispose();
                MessageBox.Show("Fin");
            }
        }

        private void button14_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPResearchAwards.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPResearchAwards.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    ResearchAward rp = hrdb.ResearchAwards.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj + "-01", out da) || da < DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    if (string.IsNullOrEmpty(t.pzdw)) continue;
                    rp.Department = t.pzdw;
                    rp.EmployeeID = id;
                    rp.Filename = t.jlzs;
                    rp.GradeCode = hrdb.DictResearchAwardGrades.Where(p => p.FName == t.jb).Select(p => p.FGradeCode).SingleOrDefault();
                    rp.AwardCode = hrdb.DictResearchAwards.Where(p => p.FName == t.dc).Select(p => p.FAwardCode).SingleOrDefault();
                    rp.TypeCode = hrdb.DictResearchAwardTypes.Where(p => p.FName == t.lb).Select(p => p.FTypeCode).SingleOrDefault();
                    if (string.IsNullOrEmpty(rp.TypeCode)) continue;
                    if (string.IsNullOrEmpty(rp.AwardCode)) continue;
                    if (string.IsNullOrEmpty(rp.GradeCode)) continue;
                    rp.Name = t.tm;
                    rp.Ordinal = int.Parse(t.wc);
                    hrdb.ResearchAwards.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button15_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPParttimeJobs.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPParttimeJobs.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    ParttimeJob rp = hrdb.ParttimeJobs.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj , out da) || da < DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    if (string.IsNullOrEmpty(t.jzmc)) continue;
                    rp.Name = t.jzmc;
                    rp.EmployeeID = id;
                    rp.Filename = t.jzzs;
                    rp.GradeCode = hrdb.DictParttimeJobGrades.Where(p => p.FName == t.jb).Select(p => p.FGradeCode).SingleOrDefault();
                    rp.HeadshipCode = hrdb.DictParttimeJobHeadships.Where(p => p.FName == t.zw).Select(p => p.FHeadshipCode).SingleOrDefault();
                    if (string.IsNullOrEmpty(rp.HeadshipCode)) continue;
                    if (string.IsNullOrEmpty(rp.GradeCode)) continue;
                    rp.Name = t.jzmc;
                    hrdb.ParttimeJobs.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button16_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPAcademicTitles.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPAcademicTitles.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    AcademicTitle rp = hrdb.AcademicTitles.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj+"-01", out da) || da < DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    if (string.IsNullOrEmpty(t.mc)) continue;
                    rp.Name = t.mc;
                    rp.EmployeeID = id;
                    rp.Filename = t.xszs;
                    rp.TypeCode = hrdb.DictAcademicTitleTypes.Where(p => p.FName == t.lb).Select(p => p.FTypeCode).SingleOrDefault();
                    if (string.IsNullOrEmpty(rp.TypeCode)) continue;
                    hrdb.AcademicTitles.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }

        private void button17_Click(object sender, EventArgs e)
        {
            hrdbEntities hrdb = new hrdbEntities(new EntityConnection(GetConnectionString()), true);
            HRJP.HRJPEntities hrjp = new HRJP.HRJPEntities();
            int total = hrjp.JPPatents.Count();
            int page = 300;
            int current = 0;
            hrjp.Dispose();
            while (page * current < total)
            {
                hrjp = new HRJP.HRJPEntities();
                var ts = hrjp.JPPatents.AsNoTracking().OrderBy(p => p.xh).Skip(page * current).Take(page);
                foreach (var t in ts)
                {
                    var ids = hrdb.TEmployees.Where(p => p.FPublicID == t.gzbm).Select(p => p.FEmployeeID);
                    if (!ids.Any()) continue;
                    string id = ids.Single();
                    Patent rp = hrdb.Patents.Create();
                    DateTime da;
                    if (!DateTime.TryParse(t.sj , out da) || da < DateTime.Parse("1900-01")) continue;
                    rp.Date = da;
                    if (string.IsNullOrEmpty(t.mc)) continue;
                    rp.Name = t.mc;
                    rp.EmployeeID = id;
                    rp.Ordinal = int.Parse(t.wc);
                    rp.Filename = t.zlzs;
                    rp.TypeCode = hrdb.DictPatentTypes.Where(p => p.FName == t.lb).Select(p => p.FTypeCode).SingleOrDefault();
                    if (string.IsNullOrEmpty(rp.TypeCode)) continue;
                    rp.DepartmentCode = "01";
                    hrdb.Patents.Add(rp);
                    hrdb.SaveChanges();
                }
                hrjp.Dispose();
                current++;
            }
            MessageBox.Show("Fin");
        }
    }
    public partial class hrdbEntities : DbContext
    {
        public hrdbEntities(DbConnection con, bool close)
            : base(con, close)
        {
        }
    }
}
